/**
 *
 */
package org.shark.jdbcTemplateTool;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.shark.jdbcTemplateTool.exception.NoColumnAnnotationFoundException;
import org.shark.jdbcTemplateTool.exception.NoDefinedGetterException;
import org.shark.jdbcTemplateTool.exception.NoIdAnnotationFoundException;
import org.shark.jdbcTemplateTool.exception.SqlFormatException;
import org.shark.jdbcTemplateTool.impl.BatchUpdateSetter;
import org.shark.jdbcTemplateTool.model.Page;
import org.shark.jdbcTemplateTool.model.SqlParamsPairs;
import org.shark.jdbcTemplateTool.utils.CacheUtil;
import org.shark.jdbcTemplateTool.utils.IdUtils;
import org.shark.jdbcTemplateTool.utils.ModelSqlUtils;
import org.shark.jdbcTemplateTool.utils.sql.SqlLoad;
import org.shark.jdbcTemplateTool.utils.sql.SqlLoadFactory;
import org.shark.jdbcTemplateTool.utils.sql.SqlLoadFactory.LoadType;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import net.sf.ehcache.CacheManager;


/**
 * @author lenovo
 * @description
 * @date 2016年8月15日
 */
public class JdbcTemplateTool implements InitializingBean {

    private JdbcTemplate jdbcTemplate;

    private String scanPackages;

    private JdbcTemplateProxy _proxy;

    private SqlLoad sqlLoad;

    private LoadType loadType;

    private String cacheName;

    private CacheManager cacheManager;

    private JdbcTemplateProxy getProxy() {
        if (_proxy == null) {
            _proxy = new JdbcTemplateProxy();
            _proxy.setJdbcTemplate(jdbcTemplate);
        }
        return _proxy;
    }

    public void init() throws SqlFormatException {
        CacheUtil.getInstant().setCacheManager(cacheManager);
        SqlLoadFactory.getInstant().setCacheName(this.getCacheName());
        SqlLoadFactory.getInstant().setLoadType(this.loadType);
        SqlLoadFactory.getInstant().init();
        this.setSqlLoad(SqlLoadFactory.getInstant().getLoad());
    }

    // --------- select ------------//

    /**
     * 获取对象列表 get a list of clazz
     *
     * @param sql
     * @param params
     * @param clazz
     * @return
     */
    @SuppressWarnings({"unchecked", "rawtypes"})
    public <T> List<T> list(String sql, Object[] params, Class<T> clazz) {

        // call jdbcTemplate to query for result
        List<T> list = null;
        if (params == null || params.length == 0) {
            list = getProxy().query(sql, new BeanPropertyRowMapper(clazz));
        } else {
            list = getProxy().query(sql, params, new BeanPropertyRowMapper(clazz));
        }

        // return list
        return list;
    }

    @SuppressWarnings({"unchecked", "rawtypes"})
    public <T> List<T> list(String sql, Map<String, Object> params, Class<T> clazz) {

        // call jdbcTemplate to query for result
        List<T> list = null;
        if (params == null) {
            list = getProxy().query(sql, new BeanPropertyRowMapper(clazz));
        } else {
            list = getProxy().query(sql, params, new BeanPropertyRowMapper(clazz));
        }

        // return list
        return list;
    }

    /**
     * 获取总行数 get count
     *
     * @param sql
     * @param params
     * @param clazz
     * @return
     */
    public int count(String sql, Object[] params) {

        int rowCount = 0;
        try {
            Map<String, Object> resultMap = null;
            if (params == null || params.length == 0) {
                resultMap = getProxy().queryForMap(sql);
            } else {
                resultMap = getProxy().queryForMap(sql, params);
            }
            Iterator<Map.Entry<String, Object>> it = resultMap.entrySet().iterator();
            if (it.hasNext()) {
                Map.Entry<String, Object> entry = it.next();
                rowCount = ((Long) entry.getValue()).intValue();
            }
        } catch (EmptyResultDataAccessException e) {

        }

        return rowCount;
    }

    /**
     * 获取总行数 get count
     *
     * @param sql
     * @param params
     * @param clazz
     * @return
     */
    public int count(String sql, Map<String, Object> params) {

        int rowCount = 0;
        try {
            Map<String, Object> resultMap = null;
            if (params == null) {
                resultMap = getProxy().queryForMap(sql);
            } else {
                resultMap = getProxy().queryForMap(sql, params);
            }
            Iterator<Map.Entry<String, Object>> it = resultMap.entrySet().iterator();
            if (it.hasNext()) {
                Map.Entry<String, Object> entry = it.next();
                rowCount = ((Long) entry.getValue()).intValue();
            }
        } catch (EmptyResultDataAccessException e) {

        }

        return rowCount;
    }

    /**
     * 获取一个对象 get object by id
     *
     * @param sql
     * @param params
     * @param clazz
     * @return
     * @throws NoIdAnnotationFoundException
     * @throws NoColumnAnnotationFoundException
     * @throws NoDefinedGetterException
     */
    @SuppressWarnings({"rawtypes", "unchecked"})
    public <T> T get(Class clazz, Object id) throws NoIdAnnotationFoundException, NoColumnAnnotationFoundException {

        // turn class to sql
        SqlParamsPairs sqlAndParams = ModelSqlUtils.getGetFromObject(clazz, id);

        // query for list
        List<T> list = this.list(sqlAndParams.getSql(), sqlAndParams.getParams(), clazz);
        if (list.size() > 0) {
            return list.get(0);
        } else {
            return null;
        }
    }

    // ---------------------------- update -----------------------------------//

    /**
     * 更新某个对象 update object
     *
     * @param po
     * @throws Exception
     */
    public int update(Object po) throws Exception {
        SqlParamsPairs sqlAndParams = ModelSqlUtils.getUpdateFromObject(po);
        return getProxy().update(sqlAndParams.getSql(), sqlAndParams.getParams());
    }

    /**
     * 更新某个对象 update object
     *
     * @param po
     * @throws Exception
     */
    public int updateNotNull(Object po) throws Exception {
        SqlParamsPairs sqlAndParams = ModelSqlUtils.getUpdateFromObjectNotNull(po);
        return getProxy().update(sqlAndParams.getSql(), sqlAndParams.getParams());
    }

    /**
     * 批量执行更新操作
     *
     * @param sql
     * @param paramsList
     */
    public void batchUpdate(String sql, List<Object[]> paramsList) {

        BatchUpdateSetter batchUpdateSetter = new BatchUpdateSetter(paramsList);

        getProxy().batchUpdate(sql, batchUpdateSetter);
    }
    
    /**
     * 执行增删改语句
     * @param sql
     * @param args
     * @return
     */
    public int executeSql(String sql, Object[] args) {
    	return getProxy().update(sql, args);
    }
    
    /**
     * 执行增删改语句
     * @param sql
     * @param args
     * @return
     */
    public int executeSql(String sql, Map<String, Object> args) {
    	return getProxy().update(sql, args);
    }

    /**
     * 保存对象的快捷方法 如果Id标定的是自增会将自增长的主键自动设置回对象 save object
     *
     * @param po
     * @throws Exception
     */
    public int save(Object po) throws Exception {
        int ret = 1;
        String autoGeneratedColumnName = IdUtils.getAutoGeneratedId(po);
        if (!"".equals(autoGeneratedColumnName)) {
            // 有自增字段
            int idValue = save(po, autoGeneratedColumnName);
            // 把自增的主键值再设置回去
            IdUtils.setAutoIncreamentIdValue(po, autoGeneratedColumnName, idValue);
        } else {
            SqlParamsPairs sqlAndParams = ModelSqlUtils.getInsertFromObject(po);

            ret = getProxy().update(sqlAndParams.getSql(), sqlAndParams.getParams());
        }
        return ret;
    }

    public int batchSave(List<Object> pos) throws Exception {
        StringBuffer sb = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        String autoGeneratedColumnName = IdUtils.getAutoGeneratedId(pos.get(0));

        for (int i = 0; i < pos.size(); i++) {
            SqlParamsPairs sqlAndParams = ModelSqlUtils.getInsertFromObject(pos.get(i));
            if (i == 0) {
                sb.append(sqlAndParams.getInsertField());
                sb.append(" values ");
            }
            sb.append(sqlAndParams.getInsertField());
            if (i < pos.size() - 1) {
                sb.append(", ");
            }
            params.addAll(Arrays.asList(sqlAndParams.getParams()));
        }

        return getProxy().insert(sb.toString(), params.toArray(), autoGeneratedColumnName);
    }

    /**
     * 保存对象并返回自增长主键的快捷方法
     *
     * @param po
     * @param autoGeneratedColumnName 自增长的主键的列名 比如 user_id
     * @throws Exception
     */
    private int save(Object po, String autoGeneratedColumnName) throws Exception {

        SqlParamsPairs sqlAndParams = ModelSqlUtils.getInsertFromObject(po);

        // 动态切换库名
        String sql = sqlAndParams.getSql();

        return getProxy().insert(sql, sqlAndParams.getParams(), autoGeneratedColumnName);
    }

    // -------------------delete-----------------//
    public void delete(Object po) throws Exception {

        SqlParamsPairs sqlAndParams = ModelSqlUtils.getDeleteFromObject(po);
        // 动态切换库名
        String sql = sqlAndParams.getSql();

        getProxy().update(sql, sqlAndParams.getParams());
    }

    public <T> Page<T> page(String sql, Map<String, Object> params, int currentPage, int pageSize, Class<T> clazz) {
        if (sql == null || sql.equals("")) {
            throw new IllegalArgumentException("Page.sql is empty");
        }

        Page<T> page = new Page<T>();

        // 设置每页显示记录数
        page.setPageSize(pageSize);
        // 设置要显示的页数
        page.setCurrentPage(currentPage);
        // 计算总记录数
        StringBuffer totalSQL = new StringBuffer(" SELECT count(*) FROM ( ");
        totalSQL.append(sql);
        totalSQL.append(" ) totalTable ");
        // 总记录数
        page.setTotalRows(this.count(totalSQL.toString(), params));
        // 计算总页数
        page.setTotalPages();
        // 计算起始行数
        page.setStartIndex();
        // 计算结束行数
        page.setLastIndex();
        // 使用mysql时直接使用limits
        StringBuffer paginationSQL = new StringBuffer();
        paginationSQL.append(sql);
        paginationSQL.append(" limit " + page.getStartIndex() + "," + page.getPageSize());
        // 装入结果集
        page.setResultList(this.list(paginationSQL.toString(), params, clazz));

        return page;
    }

    public <T> Page<T> page(String sql, Object[] params, int currentPage, int pageSize, Class<T> clazz) {
        if (sql == null || sql.equals("")) {
            throw new IllegalArgumentException("Page.sql is empty");
        }

        Page<T> page = new Page<T>();

        // 设置每页显示记录数
        page.setPageSize(pageSize);
        // 设置要显示的页数
        page.setCurrentPage(currentPage);
        // 计算总记录数
        StringBuffer totalSQL = new StringBuffer(" SELECT count(*) FROM ( ");
        totalSQL.append(sql);
        totalSQL.append(" ) totalTable ");
        // 总记录数
        page.setTotalRows(this.count(totalSQL.toString(), params));
        // 计算总页数
        page.setTotalPages();
        // 计算起始行数
        page.setStartIndex();
        // 计算结束行数
        page.setLastIndex();
        // 使用mysql时直接使用limits
        StringBuffer paginationSQL = new StringBuffer();
        paginationSQL.append(sql);
        paginationSQL.append(" limit " + page.getStartIndex() + "," + page.getPageSize());
        // 装入结果集
        page.setResultList(this.list(paginationSQL.toString(), params, clazz));

        return page;
    }

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public String getScanPackages() {
        return scanPackages;
    }

    public void setScanPackages(String scanPackages) {
        this.scanPackages = scanPackages;
    }

    public SqlLoad getSqlLoad() {
        return sqlLoad;
    }

    public void setSqlLoad(SqlLoad sqlLoad) {
        this.sqlLoad = sqlLoad;
    }

    public LoadType getLoadType() {
        return loadType;
    }

    public void setLoadType(LoadType loadType) {
        this.loadType = loadType;
    }

    public String getCacheName() {
        return cacheName;
    }

    public void setCacheName(String cacheName) {
        this.cacheName = cacheName;
    }

    public CacheManager getCacheManager() {
        return cacheManager;
    }

    public void setCacheManager(CacheManager cacheManager) {
        this.cacheManager = cacheManager;
    }

	/* (non-Javadoc)
	 * @see org.springframework.beans.factory.InitializingBean#afterPropertiesSet()
	 */
	@Override
	public void afterPropertiesSet() throws Exception {
		init();		
	}
}
